﻿

CREATE PROCEDURE P_TrendChart_PL3_HMFB --排列三号码分布图
AS

set nocount on
 
Create table #LotteryNumberListPL3(id int identity(1,1),Isuse varchar(20),LotteryNumber varchar(100),
		B_0 int,B_1 int,B_2 int,B_3 int,B_4 int,B_5 int,B_6 int,B_7 int,B_8 int,B_9 int,
		S_0 int,S_1 int,S_2 int,S_3 int,S_4 int,S_5 int,S_6 int,S_7 int,S_8 int,S_9 int,
		G_0 int,G_1 int,G_2 int,G_3 int,G_4 int,G_5 int,G_6 int,G_7 int,G_8 int,G_9 int,
		H_Z int,J_O varchar(20),D_X varchar(20),
		K_0 int,K_1 int,K_2 int,K_3 int,K_4 int,K_5 int,K_6 int,K_7 int,K_8 int,K_9 int,
		H_0 int,H_1 int,H_2 int,H_3 int,H_4 int,H_5 int,H_6 int,H_7 int,H_8 int,H_9 int)

declare @cur cursor 
declare @Isuse varchar(20), @WinLotterNumber varchar(100), @id int ,  @J_O varchar(20),@D_X varchar(20)

declare @B int,    @S int,  @G int  ,@H_Z int,@h_a varchar(10)
declare @B_0 int,@B_1 int,@B_2 int,@B_3 int,@B_4 int,@B_5 int,@B_6 int,@B_7 int,@B_8 int,@B_9 int
DECLARE @S_0 int,@S_1 int,@S_2 int,@S_3 int,@S_4 int,@S_5 int,@S_6 int,@S_7 int,@S_8 int,@S_9 int
DECLARE @G_0 int,@G_1 int,@G_2 int,@G_3 int,@G_4 int,@G_5 int,@G_6 int,@G_7 int,@G_8 int,@G_9 int
DECLARE @K_0 int,@K_1 int,@K_2 int,@K_3 int,@K_4 int,@K_5 int,@K_6 int,@K_7 int,@K_8 int,@K_9 int
DECLARE @H_0 int,@H_1 int,@H_2 int,@H_3 int,@H_4 int,@H_5 int,@H_6 int,@H_7 int,@H_8 int,@H_9 int

declare @J varchar(10),@O varchar(10),@D varchar(10),@X varchar(10)
declare @Z_D INT,@Z_X INT,@K INT,@H_G INT

Create table #tb(id int,PlayNo varchar(20),WinLotteryNumber varchar(100))


insert into #tb select top 100 Id,PlayNo,WinLotteryNumber from pm_plays where WinLotteryNumber <> '' and LotteryID = 63 order by id desc


set @G_0 = 0 set @G_1 = 0 set @G_2 = 0 set @G_3 = 0 set @G_4 = 0 set @G_5 = 0 set @G_6 = 0  set @G_7 = 0 set @G_8 = 0 set @G_9 = 0 
set @S_0 = 0 set @S_1 = 0 set @S_2 = 0 set @S_3 = 0 set @S_4 = 0 set @S_5 = 0 set @S_6 = 0  set @S_7 = 0 set @S_8 = 0 set @S_9 = 0
set @B_0 = 0 set @B_1 = 0 set @B_2 = 0 set @B_3 = 0 set @B_4 = 0 set @B_5 = 0 set @B_6 = 0  set @B_7 = 0 set @B_8 = 0 set @B_9 = 0
set @K_0 = 0 set @K_1 = 0 set @K_2 = 0 set @K_3 = 0 set @K_4 = 0 set @K_5 = 0 set @K_6 = 0  set @K_7 = 0 set @K_8 = 0 set @K_9 = 0 
set @H_0 = 0 set @H_1 = 0 set @H_2 = 0 set @H_3 = 0 set @H_4 = 0 set @H_5 = 0 set @H_6 = 0  set @H_7 = 0 set @H_8 = 0 set @H_9 = 0

set @cur = cursor FAST_FORWARD for select PlayNo,WinLotteryNumber from #tb order by id asc

open @cur

fetch next from @cur into @Isuse, @WinLotterNumber

while @@fetch_status=0
begin

set @B = Substring(@WinLotterNumber,1,1) 
set @S = Substring(@WinLotterNumber,2,1) 
set @G = Substring(@WinLotterNumber,3,1)
SET  @H_Z =@B + @G +@S
set @WinLotterNumber=substring(@WinLotterNumber,1,3)

set @G_0 = @G_0 +1  set @G_1 =@G_1 +1 set @G_2 =@G_2 +1 set @G_3 =@G_3 +1 set  @G_4 =@G_4 +1 set @G_5 =@G_5 +1 set  @G_6 =@G_6 +1 set  @G_7 =@G_7 +1 set  @G_8 =@G_8 +1 set  @G_9 =@G_9 +1 
set @S_0 = @S_0 +1  set @S_1 =@S_1 +1 set @S_2 = @S_2 +1 set @S_3 = @S_3 +1 set @S_4 = @S_4 +1 set @S_5 = @S_5 +1 set @S_6 = @S_6 +1  set @S_7 = @S_7 +1 set @S_8 =@S_8 +1 set @S_9 =@S_9 +1
set @B_0 = @B_0 + 1 set @B_1 =@B_1 + 1 set @B_2 = @B_2 + 1 set @B_3 = @B_3 + 1 set @B_4 = @B_4 + 1 set @B_5 = @B_5 + 1 set @B_6 = @B_6 + 1  set @B_7 = @B_7 + 1 set @B_8 = @B_8 + 1 set @B_9 = @B_9 + 1 
set @K_0 = @K_0+1 set @K_1 = @K_1+1 set @K_2 = @K_2+1 set @K_3 = @K_3+1 set @K_4 = @K_4+1 set @K_5 = @K_5+1 set @K_6 = @K_6+1  set @K_7 = @K_7+1 set @K_8 = @K_8+1 set @K_9 = @K_9+1
set @H_0 =@H_0 +1  set @H_1 = @H_1+1 set @H_2 = @H_2+1 set @H_3 = @H_3+1 set @H_4 = @H_4+1 set @H_5 = @H_5+1 set @H_6 = @H_6+1  set @H_7 = @H_7+1 set @H_8 = @H_8+1 set @H_9 = @H_9+1

if(@G = 0)
begin
	set @G_0 =0
end

if(@G = 1)
begin
	set @G_1 =0
end

if(@G = 2)
begin
	set @G_2 =0
end

if(@G = 3)
begin
	set @G_3 =0
end

if(@G = 4)
begin
	set @G_4 =0
end

if(@G = 5)
begin
	set @G_5 =0
end

if(@G = 6)
begin
	set @G_6 =0
end   
 
if(@G = 7)
begin
	set @G_7 =0
end   

if(@G = 8)
begin
	set @G_8 =0
end   

if(@G = 9)
begin
	set @G_9 =0
end

if(@S = 0)
begin
	set @S_0 =0
end

if(@S = 1)
begin
	set @S_1 =0
end

if(@S = 2)
begin
	set @S_2 =0
end

if(@S = 3)
begin
	set @S_3 =0
end

if(@S = 4)
begin
	set @S_4 =0
end

if(@S = 5)
begin
	set @S_5 =0
end

if(@S = 6)
begin
	set @S_6 =0
end   
 
if(@S = 7)
begin
	set @S_7 =0
end  
 
if(@S = 8)
begin
	set @S_8 =0
end   

if(@S = 9)
begin
	set @S_9 =0
end

if(@B = 0)
begin
	set @B_0 =0
end

if(@B = 1)
begin
	set @B_1 =0
end

if(@B = 2)
begin
	set @B_2 =0
end

if(@B = 3)
begin
	set @B_3 =0
end

if(@B = 4)
begin
	set @B_4 =0
end

if(@B = 5)
begin
	set @B_5 =0
end

if(@B = 6)
begin
	set @B_6 =0
end    

if(@B = 7)
begin
	set @B_7 =0
end  
 
if(@B = 8)
begin
	set @B_8 =0
end   

if(@B = 9)
begin
	set @B_9 =0
end 

if(@B % 2=1 and @S % 2=1 AND @G % 2 =1)
BEGIN
	SET  @J ='3'
END

if(@B % 2=1 and @S % 2=1 AND @G % 2 =0 OR @B % 2=1 and @S % 2=0 AND @G % 2=1 OR @B % 2=0 and @S % 2=1 AND @G % 2=1)
BEGIN
	SET  @J ='2'
END

if(@B % 2=1 and @S % 2=0 AND @G % 2 =0 OR @B % 2=0 and @S % 2=0 AND @G % 2=1 OR @B % 2=0 and @S % 2=1 AND @G % 2=0)
BEGIN
	SET  @J ='1'
END

if(@B % 2=0 and @S % 2=0 AND @G % 2 =0)
BEGIN
	SET  @J ='0'
END

if(@B % 2=1 and @S % 2=1 AND @G % 2 =1)
BEGIN
	SET  @O ='0'
END

if(@B % 2=1 and @S % 2=1 AND @G % 2 =0 OR @B % 2=1 and @S % 2=0 AND @G % 2=1 OR @B % 2=0 and @S % 2=1 AND @G % 2=1)
BEGIN
	SET  @O ='1'
END

if(@B % 2=1 and @S % 2=0 AND @G % 2 =0 OR @B % 2=0 and @S % 2=0 AND @G % 2=1 OR @B % 2=0 and @S % 2=1 AND @G % 2=0)
BEGIN
	SET  @O ='2'
END

if(@B % 2=0 and @S % 2=0 AND @G % 2 =0)
BEGIN
	SET  @O ='3'
END

set @J_O =@J+':'+@O

if(@B >4 AND @S > 4 AND @G >4)
BEGIN
	SET @D ='3'
END

if(@B >4 AND @S > 4 AND @G <5 OR @B < 5 AND @S > 4 AND @G > 4 OR @B >4 AND @S <5 AND @G >4)
BEGIN
	SET @D ='2'
END

if(@B >4 AND @S <5 AND @G <5 OR @B < 5 AND @S <5 AND @G > 4 OR @B < 5 AND @S >4 AND @G < 5)
BEGIN
	SET @D ='1'
END

if(@B <5 AND @S < 5 AND @G <5)
BEGIN
	SET @D ='0'
END

if(@B >4 AND @S > 4 AND @G >4)
BEGIN
	SET @X ='0'
END

if(@B >4 AND @S > 4 AND @G <5 OR @B < 5 AND @S > 4 AND @G > 4 OR @B >4 AND @S <5 AND @G >4)
BEGIN
	SET @X ='1'
END

if(@B >4 AND @S <5 AND @G <5 OR @B < 5 AND @S <5 AND @G > 4 OR @B < 5 AND @S >4 AND @G < 5)
BEGIN
	SET @X ='2'
END

if(@B <5 AND @S < 5 AND @G <5)
BEGIN
	SET @X ='3'
END

SET @D_X = @D+':'+@X

IF(@B>=@S AND @B>=@G)
BEGIN
	SET @Z_D = @B
END

IF(@S>=@B AND @S>=@G)
BEGIN
	SET @Z_D = @S
END

IF(@G>=@S AND @G>=@B)
BEGIN
	SET @Z_D = @G
END

IF(@B<=@S AND @B<=@G)
BEGIN
	SET @Z_X = @B
END

IF(@S<=@B AND @S<=@G)
BEGIN
	SET @Z_X = @S
END

IF(@G<=@S AND @G<=@B)
BEGIN
	SET @Z_X = @G
END

SET @K = @Z_D-@Z_X

IF(@K = 0)
BEGIN
	SET @K_0 = 0
END 

IF(@K = 1)
BEGIN
	SET @K_1 = 0
END 

IF(@K = 2)
BEGIN
	SET @K_2 = 0
END 

IF(@K = 3)
BEGIN
	SET @K_3 = 0
END 

IF(@K = 4)
BEGIN
	SET @K_4 = 0
END 

IF(@K = 5)
BEGIN
	SET @K_5 = 0
END 

IF(@K = 6)
BEGIN
	SET @K_6 = 0
END 

IF(@K = 7)
BEGIN
	SET @K_7 = 0
END 

IF(@K = 8)
BEGIN
	SET @K_8 = 0
END 

IF(@K = 9)
BEGIN
	SET @K_9 = 0
END 

IF(@H_Z < 10)
BEGIN
	SET @H_G=@H_Z
END

IF(@H_Z >=10)
BEGIN
	set @h_a = rtrim(ltrim(str(@H_Z)))
	SET @H_G =substring(@h_a,2,1)
END

IF(@H_G = 0)
BEGIN
	SET @H_0 = 0
END 

IF(@H_G = 1)
BEGIN
	SET @H_1 = 0
END 

IF(@H_G = 2)
BEGIN
	SET @H_2 = 0
END 

IF(@H_G = 3)
BEGIN
	SET @H_3 = 0
END 

IF(@H_G = 4)
BEGIN
	SET @H_4 = 0
END 

IF(@H_G = 5)
BEGIN
	SET @H_5 = 0
END 

IF(@H_G = 6)
BEGIN
	SET @H_6 = 0
END 

IF(@H_G = 7)
BEGIN
	SET @H_7 = 0
END 

IF(@H_G = 8)
BEGIN
	SET @H_8 = 0
END 

IF(@H_G = 9)
BEGIN
	SET @H_9 = 0
END 

insert into #LotteryNumberListPL3 values (@Isuse, @WinLotterNumber, @B_0 ,@B_1,@B_2 ,@B_3 ,@B_4 ,@B_5,@B_6 ,@B_7 ,@B_8,@B_9,
		@S_0 ,@S_1,@S_2 ,@S_3,@S_4,@S_5 ,@S_6 ,@S_7 ,@S_8 ,@S_9 ,
		@G_0 ,@G_1 ,@G_2 ,@G_3 ,@G_4 ,@G_5 ,@G_6 ,@G_7 ,@G_8 ,@G_9 ,@H_Z,@J_O,@D_X, @K_0 ,@K_1 ,@K_2 ,@K_3 ,@K_4 ,@K_5 ,@K_6 ,@K_7 ,@K_8 ,@K_9 ,
		@H_0 ,@H_1 ,@H_2 ,@H_3 ,@H_4 ,@H_5 ,@H_6 ,@H_7 ,@H_8 ,@H_9 )
					
fetch next from @cur into @Isuse, @WinLotterNumber

end

close @cur

select * from #LotteryNumberListPL3

GO


exec P_TrendChart_PL3_HMFB